package cx.zan.dao;

import cx.zan.domain.PageBean;
import cx.zan.domain.Product;
import cx.zan.utils.DataSourceUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * @author Mxb
 * @version 1.0
 * @date 2020/4/1 9:42
 */
public class ProductDao {
    /**
     * 查询热门商品
     *
     * @return
     * @throws SQLException
     */
    public List<Product> findhot() throws SQLException {
        //注入数据源
        QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource());

        String sql = "select * from product where is_hot = 1";
        //查询集合 使用 BeanListHandler
        List<Product> list = queryRunner.query(sql, new BeanListHandler<>(Product.class));
        return list;
    }

    /**
     * 根据分类cid查询
     *
     * @param cid
     * @return
     */
    public List<Product> findProductByCid(String cid) {
        //注入数据源
        QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource());

        String sql = "select * from product where cid=?";
        //查询集合 使用 BeanListHandler
        List<Product> list = null;
        try {
            list = queryRunner.query(sql, new BeanListHandler<>(Product.class), cid);
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }

    /**
     * 根据pid查询
     *
     * @param pid
     * @return
     */
    public Product findById(String pid) {
        //注入数据源
        QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource());

        String sql = "select * from product where pid=?";
        //查询集合 使用 BeanListHandler
        Product product = null;
        try {
            product = queryRunner.query(sql, new BeanHandler<>(Product.class), pid);
            return product;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }

    /**
     * 分页查询
     *
     * @param cid
     * @param pageBean
     * @return
     */
    public List<Product> findProductByCidPageBean(String cid, PageBean pageBean) {
        //注入数据源
        QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource());

        String sql = "select * from product where cid = ? limit ?,?";
        //查询集合 使用 BeanListHandler
        List<Product> list = null;
        try {
            list = queryRunner.query(sql, new BeanListHandler<>(Product.class), cid, pageBean.startIndex(),
                    pageBean.getPageSize());
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException();
        }

    }

    /**
     * 总条数
     *
     * @param cid
     * @return
     */
    public Long totalCountByCid(String cid) {
        //注入数据源
        QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource());

        String sql = "select count(*) from product where cid=?";
        //查询集合 使用 BeanListHandler
        //Product product = null;
        try {
            //计算单值
            Long query = (Long) queryRunner.query(sql, new ScalarHandler(), cid);
            return query;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }

    /**
     * 查询所有商品
     * @return
     */
    public List<Product> findAll() {
        //注入数据源
        QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource());

        String sql = "select * from product";
        //查询集合 使用 BeanListHandler
        try {
            //
            List<Product> list = queryRunner.query(sql, new BeanListHandler<>(Product.class));
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException();
        }

    }

    /**
     * 添加商品
     *
     * @param product
     */
    public void insert(Product product) {
        //注入数据源
        QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource());

        String sql = "insert into product values(?,?,?,?,?,?,?,?,?,?)";
        //查询集合 使用 BeanListHandler
        try {
            //
            queryRunner.update(sql, product.getPid(), product.getPname(), product.getMarket_price()
                    , product.getShop_price(), product.getPimage(), product.getPdate(), product.getIs_hot(),
                    product.getPdesc(), product.getPflag(), product.getCid());
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }
}
